package dao;

import classes.Servidor;
import classes.Tarefa;
import classes.TarefaServidor;
import conexao.GerenciadorConexao;
import controlador.LoginControlador;
import erro.ConexaoException;
import erro.ConsultaException;
import gui.TarefaMostrar;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Date;
import java.util.GregorianCalendar;
import java.util.List;

/**
 *
 * @author Roberta
 */
public class TarefaDao {

    private GerenciadorConexao g;

    public TarefaDao() {
        g = GerenciadorConexao.getInstancia();
    }

    /**
     * 
     * @param t (Tarefa)
     * @param ts (TarefaServidor)
     * @throws ConexaoException
     * @throws ConsultaException 
     */
    public void inserirTarefa(Tarefa t, TarefaServidor ts) throws ConexaoException, ConsultaException {
        String sql = "INSERT INTO tarefa (tarefa_id,detalhe,prioridade,atividade,dataInclusao,dataPrevista,Tstatus,matricula) "
                + "VALUES (?,?,?,?,?,?,?,?)";
        String sqlTs = "INSERT INTO tarefaservidor (tarefa_id,matricula,nome,email) VALUES (?,?,?,?)";
        List<Servidor> listaS = ts.getServidor();
        int size = listaS.size();
        try {
            g.connect().setAutoCommit(false);
            PreparedStatement pstm = (PreparedStatement) g.connect().prepareStatement(sql);
            PreparedStatement pstmTs = (PreparedStatement) g.connect().prepareStatement(sqlTs);
            GregorianCalendar calendar = new GregorianCalendar();
            Date dataInclusao = calendar.getTime();
            t.setDataInclusão(dataInclusao);
            Date dataFinal = t.getDataPrevista();
            t.setDataPrevista(dataFinal);
            pstm.setInt(1, t.getTarefa_id());
            pstm.setString(2, t.getDetalhe());
            pstm.setInt(3, t.getPrioridade());
            pstm.setString(4, t.getAtividade());
            pstm.setDate(5, new java.sql.Date(dataInclusao.getTime()));
            pstm.setDate(6, new java.sql.Date(dataFinal.getTime()));
            pstm.setInt(7, 0);
            pstm.setInt(8, t.getSolicitante().getMatricula());
            pstm.executeUpdate();
            pstmTs.setInt(1, ts.getTarefa_id().getTarefa_id());
            for (int i = 0; i < size; i++) {
                pstmTs.setInt(2, listaS.get(i).getMatricula());
                pstmTs.setString(3, listaS.get(i).getNome());
                pstmTs.setString(4, listaS.get(i).getEmailProf());
                pstmTs.executeUpdate();
            }
            g.connect().commit();
        } catch (SQLException ex) {
            throw new ConsultaException(ex);
        } finally {
            g.disconnect();
        }
    }

    /**
     * 
     * @param t (Tarefa)
     * @param ts (TarefaServidor)
     * @throws ConexaoException
     * @throws ConsultaException 
     */
    public void alterarTarefa(Tarefa t, TarefaServidor ts) throws ConexaoException, ConsultaException {
        String sql = "UPDATE tarefa SET detalhe=?,prioridade=?,atividade=?,dataPrevista=?,dataInclusao=? WHERE tarefa_id=?";
        String sqlTs = "INSERT INTO tarefaservidor (tarefa_id,matricula,nome,email) VALUES (?,?,?,?)";
        String sqlD = "DELETE FROM tarefaservidor WHERE tarefa_id=?";
        List<Servidor> listaS = ts.getServidor();
        int size = listaS.size();
        try {
            g.connect().setAutoCommit(false);
            PreparedStatement pstm = (PreparedStatement) g.connect().prepareStatement(sql);
            PreparedStatement pstmTs = (PreparedStatement) g.connect().prepareStatement(sqlTs);
            PreparedStatement pstmD = (PreparedStatement) g.connect().prepareStatement(sqlD);
            Date dataFinal = t.getDataPrevista();
            t.setDataPrevista(dataFinal);
            pstm.setString(1, t.getDetalhe());
            pstm.setInt(2, t.getPrioridade());
            pstm.setString(3, t.getAtividade());
            pstm.setDate(4, new java.sql.Date(dataFinal.getTime()));
            pstm.setDate(5, new java.sql.Date(t.getDataInclusão().getTime()));
            pstm.setInt(6, t.getTarefa_id());
            pstm.executeUpdate();
            pstmD.setInt(1, ts.getTarefa_id().getTarefa_id());
            pstmD.executeUpdate();
            pstmTs.setInt(1, ts.getTarefa_id().getTarefa_id());
            for (int i = 0; i < size; i++) {
                pstmTs.setInt(2, listaS.get(i).getMatricula());
                pstmTs.setString(3, listaS.get(i).getNome());
                pstmTs.setString(4, listaS.get(i).getEmailProf());
                pstmTs.executeUpdate();
            }
            g.connect().commit();
        } catch (SQLException ex) {
            throw new ConsultaException(ex);
        } finally {
            g.disconnect();
        }
    }

    /**
     * 
     * @param t (Tarefa)
     * @throws ConsultaException
     * @throws ConexaoException 
     */
    public void InserirDataInicial(Tarefa t) throws ConsultaException, ConexaoException {
        String sql = "UPDATE tarefa SET dataInicial=?,Tstatus=? WHERE tarefa_id=?";

        try {
            PreparedStatement pstm = (PreparedStatement) g.connect().prepareStatement(sql);
            Date dataInicial = t.getDataInicial();
            t.setDataInicial(dataInicial);
            pstm.setDate(1, new java.sql.Date(dataInicial.getTime()));
            pstm.setInt(2, t.getStatus());

            pstm.setInt(3, t.getTarefa_id());
            pstm.executeUpdate();
        } catch (SQLException ex) {
            throw new ConsultaException(ex);
        } finally {
            g.disconnect();
        }
    }

    /**
     * 
     * @param t (Tarefa)
     * @throws ConsultaException
     * @throws ConexaoException 
     */
    public void InserirDataConclusao(Tarefa t) throws ConsultaException, ConexaoException {
        String sql = "UPDATE tarefa SET dataConclusao=?,Tstatus=? WHERE tarefa_id=?";

        try {
            PreparedStatement pstm = (PreparedStatement) g.connect().prepareStatement(sql);
            Date dataConclusao = new Date();
            t.setDataConclusão(dataConclusao);
            pstm.setDate(1, new java.sql.Date(dataConclusao.getTime()));
            pstm.setInt(2, t.getStatus());

            pstm.setInt(3, t.getTarefa_id());
            pstm.executeUpdate();
        } catch (SQLException ex) {
            throw new ConsultaException(ex);
        } finally {
            g.disconnect();
        }
    }

    /**
     * 
     * @param t (Tarefa)
     * @return
     * @throws ConexaoException
     * @throws ConsultaException 
     */
    public Tarefa consultarid(Tarefa t) throws ConexaoException, ConsultaException {
        String sql = "SELECT tarefa_id FROM tarefa ORDER BY tarefa_id DESC LIMIT 1";
        try {
            PreparedStatement pstm = g.connect().prepareStatement(sql);
            ResultSet rs = pstm.executeQuery();
            if (rs.next()) {
                t.setTarefa_id(rs.getInt("tarefa_id"));
            }
        } catch (SQLException ex) {
            throw new ConsultaException(ex);
        } finally {
            g.disconnect();
        }
        return t;
    }

    /**
     * 
     * @return
     * @throws ConexaoException
     * @throws ConsultaException 
     */
    public List<Tarefa> listar() throws ConexaoException, ConsultaException {
        List<Tarefa> lista = new ArrayList<Tarefa>();
        Tarefa t;
        String sql = "SELECT * FROM tarefa WHERE Tstatus<6 ORDER BY Tstatus ASC";
        try {
            Statement stm = (Statement) g.connect().createStatement();
            ResultSet rs = stm.executeQuery(sql);

            while (rs.next()) {
                t = new Tarefa();
                t.setTarefa_id(rs.getInt("tarefa_id"));
                t.setAtividade(rs.getString("atividade"));
                t.setDataInclusão(rs.getDate("dataInclusao"));
                t.setDataPrevista(rs.getDate("dataPrevista"));
                t.setStatus(rs.getInt("Tstatus"));
                t.setDataInicial(rs.getDate("dataInicial"));
                lista.add(t);
            }
        } catch (SQLException ex) {
            throw new ConsultaException(ex);
        } finally {
            g.disconnect();
        }
        return lista;
    }

    /**
     * 
     * @return
     * @throws ConexaoException
     * @throws ConsultaException 
     */
    public List<TarefaServidor> listarTS() throws ConexaoException, ConsultaException {
        List<TarefaServidor> lista = new ArrayList<TarefaServidor>();
        ArrayList<Servidor> listaS;
        Tarefa t;
        TarefaServidor ts = new TarefaServidor();
        Servidor s;
        String sql = "SELECT * FROM tarefaservidor as ts INNER JOIN tarefa as t ON  t.tarefa_id=ts.tarefa_id "
                + "AND t.Tstatus<6 ORDER BY t.Tstatus ASC";
        try {
            Statement stm = (Statement) g.connect().createStatement();
            ResultSet rs = stm.executeQuery(sql);
            while (rs.next()) {
                t = new Tarefa();
                s = new Servidor();
                listaS = new ArrayList<Servidor>();
                t.setTarefa_id(rs.getInt("t.tarefa_id"));
                t.setAtividade(rs.getString("t.atividade"));
                t.setDataInclusão(rs.getDate("t.dataInclusao"));
                t.setDataPrevista(rs.getDate("t.dataPrevista"));
                t.setStatus(rs.getInt("t.Tstatus"));
                t.setDataInicial(rs.getDate("t.dataInicial"));
                s.setNome(rs.getString("ts.nome"));
                listaS.add(s);
                t.setListaServ(listaS);
                lista.add(ts);
            }
        } catch (SQLException ex) {
            throw new ConsultaException(ex);
        } finally {
            g.disconnect();
        }
        return lista;
    }

    /**
     * 
     * @param t (Tarefa)
     * @param ts (TarefaServidor)
     * @throws ConexaoException
     * @throws ConsultaException 
     */
    public void deletarTarefa(Tarefa t, TarefaServidor ts) throws ConexaoException, ConsultaException {
        String sql = "DELETE FROM tarefa WHERE tarefa_id=?";
        String sqlTs = "DELETE FROM tarefaservidor WHERE tarefa_id=?";

        try {
            g.connect().setAutoCommit(false);
            PreparedStatement pstmTs = (PreparedStatement) g.connect().prepareStatement(sqlTs);
            PreparedStatement pstm = (PreparedStatement) g.connect().prepareStatement(sql);
            pstmTs.setInt(1, TarefaMostrar.mat);
            pstmTs.executeUpdate();

            pstm.setInt(1, t.getTarefa_id());
            pstm.executeUpdate();
            g.connect().commit();
        } catch (SQLException ex) {
            throw new ConsultaException(ex);
        } finally {
            g.disconnect();
        }
    }

    /**
     * 
     * @return
     * @throws ConexaoException
     * @throws ConsultaException 
     */
    public List<Tarefa> listarStatus() throws ConexaoException, ConsultaException {
        List<Tarefa> lista = new ArrayList<Tarefa>();
        Tarefa t;
        String sql = "SELECT * FROM tarefa WHERE Tstatus<=5";
        try {
            Statement stm = (Statement) g.connect().createStatement();
            ResultSet rs = stm.executeQuery(sql);

            while (rs.next()) {
                t = new Tarefa();
                t.setTarefa_id(rs.getInt("tarefa_id"));
                t.setStatus(rs.getInt("Tstatus"));
                t.setDataInclusão(rs.getDate("dataInclusao"));
                t.setDataPrevista(rs.getDate("dataPrevista"));
                t.setDataInicial(rs.getDate("dataInicial"));
                t.setDataConclusão(rs.getDate("dataConclusao"));
                lista.add(t);
            }
        } catch (SQLException ex) {
            throw new ConsultaException(ex);
        } finally {
            g.disconnect();
        }
        return lista;
    }

    /**
     * 
     * @return
     * @throws ConexaoException
     * @throws ConsultaException 
     */
    public List<Tarefa> listarPorSolicitante() throws ConexaoException, ConsultaException {
        List<Tarefa> lista = new ArrayList<Tarefa>();
        String lc = LoginControlador.login; //Pega o login.
        Servidor s = new Servidor();
        Tarefa t = new Tarefa();
        ServidorDao sdao = new ServidorDao();
        s.setLogin(lc);
        sdao.pesquisarLogin(s);
        t.setSolicitante(s);
        String sql = "SELECT * FROM tarefa WHERE matricula=?";
        try {
            PreparedStatement pstm = g.connect().prepareStatement(sql);
            pstm.setInt(1, t.getSolicitante().getMatricula());
            ResultSet rs = pstm.executeQuery();
            while (rs.next()) {
                t = new Tarefa();
                t.setTarefa_id(rs.getInt("tarefa_id"));
                t.setAtividade(rs.getString("atividade"));
                t.setStatus(rs.getInt("Tstatus"));
                t.setDataInclusão(rs.getDate("dataInclusao"));
                t.setDataPrevista(rs.getDate("dataPrevista"));
                t.setDataInicial(rs.getDate("dataInicial"));
                lista.add(t);
            }
        } catch (SQLException ex) {
            throw new ConsultaException(ex);
        } finally {
            g.disconnect();
        }
        return lista;
    }

    /**
     * 
     * @return
     * @throws ConexaoException
     * @throws ConsultaException 
     */
    public List<Tarefa> listarConcluidas() throws ConexaoException, ConsultaException {
        List<Tarefa> lista = new ArrayList<Tarefa>();
        Tarefa t;
        String sql = "SELECT * FROM tarefa WHERE Tstatus = 6";
        try {
            Statement stm = (Statement) g.connect().createStatement();
            ResultSet rs = stm.executeQuery(sql);

            while (rs.next()) {
                t = new Tarefa();
                t.setTarefa_id(rs.getInt("tarefa_id"));
                t.setStatus(rs.getInt("Tstatus"));
                t.setAtividade(rs.getString("atividade"));
                t.setDataInclusão(rs.getDate("dataInclusao"));
                t.setDataPrevista(rs.getDate("dataPrevista"));
                t.setDataInicial(rs.getDate("dataInicial"));
                t.setDataConclusão(rs.getDate("dataConclusao"));
                lista.add(t);
            }
        } catch (SQLException ex) {
            throw new ConsultaException(ex);
        } finally {
            g.disconnect();
        }
        return lista;
    }

    /**
     * 
     * @return
     * @throws ConexaoException
     * @throws ConsultaException 
     */
    public List<Tarefa> listarParticipo() throws ConexaoException, ConsultaException {
        List<Tarefa> lista = new ArrayList<Tarefa>();
        Tarefa t = new Tarefa();
        String lc = LoginControlador.login;
        Servidor s = new Servidor();
        ServidorDao sdao = new ServidorDao();
        s.setLogin(lc);
        sdao.pesquisarLogin(s);
        t.setSolicitante(s);
        String sql = "SELECT t.*, ts.* FROM tarefa as t,tarefaservidor as ts "
                + "WHERE ts.matricula=? AND ts.tarefa_id=t.tarefa_id";
        try {
            PreparedStatement pstm = g.connect().prepareStatement(sql);
            pstm.setInt(1, t.getSolicitante().getMatricula());
            ResultSet rs = pstm.executeQuery();

            while (rs.next()) {
                t = new Tarefa();
                t.setTarefa_id(rs.getInt("t.tarefa_id"));
                t.setStatus(rs.getInt("t.Tstatus"));
                t.setAtividade(rs.getString("t.atividade"));
                t.setDataInclusão(rs.getDate("t.dataInclusao"));
                t.setDataPrevista(rs.getDate("t.dataPrevista"));
                t.setDataInicial(rs.getDate("t.dataInicial"));
                t.setDataConclusão(rs.getDate("t.dataConclusao"));
                lista.add(t);
            }
        } catch (SQLException ex) {
            throw new ConsultaException(ex);
        } finally {
            g.disconnect();
        }
        return lista;
    }

    /**
     * 
     * @return
     * @throws ConexaoException
     * @throws ConsultaException 
     */
    public List<Tarefa> listarTodas() throws ConexaoException, ConsultaException {
        List<Tarefa> lista = new ArrayList<Tarefa>();
        Tarefa t;
        String sql = "SELECT * FROM tarefa ORDER BY Tstatus DESC";
        try {
            Statement stm = (Statement) g.connect().createStatement();
            ResultSet rs = stm.executeQuery(sql);
            while (rs.next()) {
                t = new Tarefa();
                t.setTarefa_id(rs.getInt("tarefa_id"));
                t.setStatus(rs.getInt("Tstatus"));
                t.setAtividade(rs.getString("atividade"));
                t.setDataInclusão(rs.getDate("dataInclusao"));
                t.setDataPrevista(rs.getDate("dataPrevista"));
                t.setDataInicial(rs.getDate("dataInicial"));
                t.setDataConclusão(rs.getDate("dataConclusao"));
                lista.add(t);
            }
        } catch (SQLException ex) {
            throw new ConsultaException(ex);
        } finally {
            g.disconnect();
        }
        return lista;
    }

    /**
     * 
     * @return
     * @throws ConexaoException
     * @throws ConsultaException 
     */
    public List<Tarefa> listarNaoIniciadas() throws ConexaoException, ConsultaException {
        List<Tarefa> lista = new ArrayList<Tarefa>();
        Tarefa t;
        String sql = "SELECT * FROM tarefa WHERE Tstatus <=2";
        try {
            Statement stm = (Statement) g.connect().createStatement();
            ResultSet rs = stm.executeQuery(sql);

            while (rs.next()) {
                t = new Tarefa();
                t.setTarefa_id(rs.getInt("tarefa_id"));
                t.setStatus(rs.getInt("Tstatus"));
                t.setAtividade(rs.getString("atividade"));
                t.setDataInclusão(rs.getDate("dataInclusao"));
                t.setDataPrevista(rs.getDate("dataPrevista"));
                t.setDataInicial(rs.getDate("dataInicial"));
                t.setDataConclusão(rs.getDate("dataConclusao"));
                lista.add(t);
            }
        } catch (SQLException ex) {
            throw new ConsultaException(ex);
        } finally {
            g.disconnect();
        }
        return lista;
    }

    /**
     * 
     * @return
     * @throws ConexaoException
     * @throws ConsultaException 
     */
    public List<Tarefa> listarAtrasadas() throws ConexaoException, ConsultaException {
        List<Tarefa> lista = new ArrayList<Tarefa>();
        Tarefa t;
        String sql = "SELECT * FROM tarefa WHERE Tstatus = 5 OR Tstatus = 2";
        try {
            Statement stm = (Statement) g.connect().createStatement();
            ResultSet rs = stm.executeQuery(sql);

            while (rs.next()) {
                t = new Tarefa();
                t.setTarefa_id(rs.getInt("tarefa_id"));
                t.setStatus(rs.getInt("Tstatus"));
                t.setAtividade(rs.getString("atividade"));
                t.setDataInclusão(rs.getDate("dataInclusao"));
                t.setDataPrevista(rs.getDate("dataPrevista"));
                t.setDataInicial(rs.getDate("dataInicial"));
                t.setDataConclusão(rs.getDate("dataConclusao"));
                lista.add(t);
            }
        } catch (SQLException ex) {
            throw new ConsultaException(ex);
        } finally {
            g.disconnect();
        }
        return lista;
    }

    /**
     * 
     * @return
     * @throws ConexaoException
     * @throws ConsultaException 
     */
    public List<Tarefa> listarExcluidas() throws ConexaoException, ConsultaException {
        List<Tarefa> lista = new ArrayList<Tarefa>();
        Tarefa t;
        String sql = "SELECT * FROM tarefa WHERE Tstatus= 7";
        try {
            Statement stm = (Statement) g.connect().createStatement();
            ResultSet rs = stm.executeQuery(sql);

            while (rs.next()) {
                t = new Tarefa();
                t.setTarefa_id(rs.getInt("tarefa_id"));
                t.setStatus(rs.getInt("Tstatus"));
                t.setAtividade(rs.getString("atividade"));
                t.setDataInclusão(rs.getDate("dataInclusao"));
                t.setDataPrevista(rs.getDate("dataPrevista"));
                t.setDataInicial(rs.getDate("dataInicial"));
                t.setDataConclusão(rs.getDate("dataConclusao"));
                lista.add(t);
            }
        } catch (SQLException ex) {
            throw new ConsultaException(ex);
        } finally {
            g.disconnect();
        }
        return lista;
    }

    /**
     * 
     * @param t (Tarefa)
     * @throws ConsultaException
     * @throws ConexaoException 
     */
    public void alterarStatus(Tarefa t) throws ConsultaException, ConexaoException {
        String sql = "UPDATE tarefa SET Tstatus=? WHERE tarefa_id=?";

        try {
            PreparedStatement pstm = (PreparedStatement) g.connect().prepareStatement(sql);
            pstm.setInt(1, t.getStatus());

            pstm.setInt(2, t.getTarefa_id());
            pstm.executeUpdate();
        } catch (SQLException ex) {
            throw new ConsultaException(ex);
        } finally {
            g.disconnect();
        }
    }

    /**
     * 
     * @param t (Tarefa)
     * @return
     * @throws ConexaoException
     * @throws ConsultaException 
     */
    public Tarefa pesquisar(Tarefa t) throws ConexaoException, ConsultaException {
        Servidor s = new Servidor();
        String sql = "SELECT * FROM tarefa WHERE tarefa_id=?";
        try {
            PreparedStatement pstm = g.connect().prepareStatement(sql);
            pstm.setInt(1, t.getTarefa_id());
            ResultSet rs = pstm.executeQuery();
            if (rs.next()) {
                t.setTarefa_id(rs.getInt("tarefa_id"));
                t.setAtividade(rs.getString("atividade"));
                t.setDetalhe(rs.getString("detalhe"));
                t.setDataInclusão(rs.getDate("dataInclusao"));
                t.setDataPrevista(rs.getDate("dataPrevista"));
                t.setPrioridade(rs.getInt("prioridade"));
                t.setDataInicial(rs.getDate("dataInicial"));
                t.setDataConclusão(rs.getDate("dataConclusao"));
                t.setStatus(rs.getInt("Tstatus"));
                s.setMatricula(rs.getInt("matricula"));
                t.setSolicitante(s);
            }
        } catch (SQLException ex) {
            throw new ConsultaException(ex);
        } finally {
            g.disconnect();
        }
        return t;
    }

    /**
     * 
     * @return
     * @throws ConexaoException
     * @throws ConsultaException 
     */
    public List<TarefaServidor> pesquisarTs() throws ConexaoException, ConsultaException {

        String sql = "SELECT ts.tarefa_id,ts.tf_id,ts.nome,t.tarefa_id "
                + "FROM tarefaservidor as ts INNER JOIN tarefa as t ON  t.tarefa_id=ts.tarefa_id "
                + "AND t.tarefa_id=?";
        TarefaServidor ts;
        Tarefa tarefa;
        List<TarefaServidor> lista = new ArrayList<TarefaServidor>();
        try {
            PreparedStatement pstm = g.connect().prepareStatement(sql);
            pstm.setInt(1, TarefaMostrar.mat);
            ResultSet rs = pstm.executeQuery();
            while (rs.next()) {
                tarefa = new Tarefa();
                ts = new TarefaServidor();
                tarefa.setTarefa_id(rs.getInt("ts.tarefa_id"));
                ts.setTf_id(rs.getInt("ts.tf_id"));
                ts.setTarefa_id(tarefa);
                ts.setNome(rs.getString("ts.nome"));
                lista.add(ts);
            }
        } catch (SQLException ex) {
            throw new ConsultaException(ex);
        } finally {
            g.disconnect();
        }
        return lista;
    }

    public List<TarefaServidor> listarTs() throws ConexaoException, ConsultaException {
        String sql = "SELECT s.nomeExibido,ts.tarefa_id,ts.tf_id,ts.nome,t.tarefa_id "
                + "FROM tarefaservidor ts ,tarefa t, servidor s "
                + "WHERE  t.tarefa_id=ts.tarefa_id "
                + "AND s.matricula = ts.matricula "
                + "AND t.tarefa_id=?";
        TarefaServidor ts;
        Tarefa tarefa;
        List<TarefaServidor> lista = new ArrayList<TarefaServidor>();
        try {
            PreparedStatement pstm = g.connect().prepareStatement(sql);
            pstm.setInt(1, TarefaServidor.tarefa);
            ResultSet rs = pstm.executeQuery();
            while (rs.next()) {
                tarefa = new Tarefa();
                ts = new TarefaServidor();
                tarefa.setTarefa_id(rs.getInt("ts.tarefa_id"));
                ts.setTf_id(rs.getInt("ts.tf_id"));
                ts.setTarefa_id(tarefa);
                ts.setNome(rs.getString("s.nomeExibido"));
                lista.add(ts);
            }
        } catch (SQLException ex) {
            throw new ConsultaException(ex);
        } finally {
            g.disconnect();
        }
        return lista;
    }

    public List<Servidor> listarTP(Tarefa t) throws ConexaoException, ConsultaException {
        String sql = "SELECT ts.nome,ts.matricula,s.nomeExibido,s.email,s.emailPessoal,s.emailProf "
                + "FROM tarefaservidor as ts,tarefa as t, servidor s "
                + "WHERE t.tarefa_id = ts.tarefa_id "
                + "AND s.matricula = ts.matricula "
                + "AND t.tarefa_id=? ORDER BY ts.nome ASC";
        Servidor s;
        ArrayList<Servidor> servidor = new ArrayList<Servidor>();
        try {
            PreparedStatement pstm = g.connect().prepareStatement(sql);
            pstm.setInt(1, t.getTarefa_id());
            ResultSet rs = pstm.executeQuery();
            while (rs.next()) {
                s = new Servidor();
                s.setMatricula(rs.getInt("ts.matricula"));
                s.setNome(rs.getString("ts.nome"));
                s.setNomeExibicao(rs.getString("s.nomeExibido"));
                s.setEmail(rs.getInt("s.email"));
                s.setEmailPessoal(rs.getString("s.emailPessoal"));
                s.setEmailProf(rs.getString("s.emailProf"));
                servidor.add(s);
            }
        } catch (SQLException ex) {
            throw new ConsultaException(ex);
        } finally {
            g.disconnect();
        }
        return servidor;
    }
}
